package com.olap.starter.controller.admin.luckycola;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.annotation.DbType;
import com.olap.starter.common.service.CRUDServices;
import com.olap.starter.common.util.OnlineUtils;
import com.olap.starter.common.vo.ModifyParameter;
import com.olap.starter.common.vo.QueryParameter;
import com.olap.starter.common.vo.SaveParameter;
import com.olap.starter.config.dataSource.JdbcTemplateSource;
import com.olap.starter.controller.admin.luckycola.utils.OracleQuery;
import com.olap.starter.controller.admin.luckycola.utils.SqlUtil;
import com.olap.starter.core.response.Result;
import com.olap.starter.core.response.ResultUtil;
import com.olap.starter.controller.admin.luckycola.services.AsyncDBServices;
import com.olap.starter.controller.admin.luckycola.services.OracleAsyncDBService;
import com.olap.starter.controller.admin.luckycola.utils.MySqlQuery;
import com.olap.starter.pojo.TableDelete;
import com.olap.starter.util.StringUtils;
import lombok.extern.log4j.Log4j2;
import net.sf.jsqlparser.JSQLParserException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

import static com.olap.starter.common.util.QueryGeneratorUtil.getExecuteSql;
import static com.olap.starter.core.response.ResultUtil.success;

@Log4j2
@RestController
@RequestMapping("/online/asyncDb")
public class AsyncDBController {

    @Autowired
    private JdbcTemplateSource jdbcTemplateSource;
    @Autowired
    private CRUDServices crudServices;

    @Autowired
    private ChineseToEnglish chineseToEnglish;

    @Autowired
    private AsyncDBServices asyncDBServices;

    @Autowired
    private OracleAsyncDBService oracleAsyncDBService;

    @GetMapping("/getTables")
    public Result getTables(String table_schema,String table_comment,String table_name) {
        Connection connection = null;
        log.info("获取getTables");
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getNewJdbcTemplateMap(table_schema);
        log.info("获取getTables成功");
        List<String> list = new ArrayList<>();

        try {
            jdbcTemplate.setFetchSize(1000);
            String databaseName = SqlUtil.getDbType(jdbcTemplate);
            String newDbName = SqlUtil.getNewDbName(databaseName, jdbcTemplate);
            if (newDbName == null) {
                newDbName = table_schema;
            }
            log.info("newDbName：" + newDbName);
            list.add(newDbName);
            String sql;
            if (DbType.ORACLE.getDb().equals(databaseName)) {
                sql = new OracleQuery().tablesSql();
                if (StringUtils.isNotNull(table_comment)) {
                    sql += " and  comments like ?";
                    list.add("%" + table_comment + "%");
                }
            } else {
                sql = new MySqlQuery().getITableSql();
                if (StringUtils.isNotNull(table_comment)) {
                    sql += " and  table_comment like ?";
                    list.add("%" + table_comment + "%");
                }
            }

            if (StringUtils.isNotNull(table_name)) {
                sql += " and UPPER(table_name) like ?";
                list.add("%" + table_name.toString().toUpperCase() + "%");
            }
            long start = System.currentTimeMillis();
            log.info("\n执行SQL：" + sql + "\n参数：" + JSONObject.toJSONString(list));
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, list.toArray());
            //List<Map<String, Object>> maps = executeQuery(sql, list.toArray());
            log.info("\n执行SQL1耗时：" + (System.currentTimeMillis() - start));
            for (Map<String, Object> map : maps) {
                map.put("datasource",table_schema);
            }
            if (DbType.ORACLE.getDb().equals(databaseName)) {
                maps = convertToLowerCaseKeys(maps);
            }
            return success(maps);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    // 处理关闭连接时可能发生的异常
                }
            }
        }
    }

    public static List<Map<String, Object>> convertToLowerCaseKeys(List<Map<String, Object>> list) {
        List<Map<String, Object>> newList = new ArrayList<>();
        for (Map<String, Object> map : list) {
            Map<String, Object> newMap = new HashMap<>();
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                newMap.put(entry.getKey().toLowerCase(), entry.getValue());
            }
            newList.add(newMap);
        }
        return newList;
    }

    @GetMapping("/chineseToEnglish")
    public Result getFiled(String dbName, String text) {
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getNewJdbcTemplateMap(dbName);
        StringBuilder sb = new StringBuilder();
        List<Object> objectList = new ArrayList<>();
        Connection connection = null;
        try {
            String like;
            jdbcTemplate.setFetchSize(1000);
            String databaseName = SqlUtil.getDbType(jdbcTemplate);
            String newDbName = SqlUtil.getNewDbName(databaseName, jdbcTemplate);
            if (DbType.ORACLE.getDb().equals(databaseName)) {
                // 获取数据库产品名称
                sb.append(new OracleQuery().getCommentSql());
                if (text.length() > 0) {
                    sb.append(" where ");
                }
                like = " COMMENTS like ? ";
            } else {
                sb.append(new MySqlQuery().getCommentSql());
                if (text.length() > 0) {
                    sb.append(" and (");

                }
                objectList.add(newDbName);
                like = " COLUMN_COMMENT like ?  ";
            }
            for (int i = 0; i < text.length(); i++) {
                if (i != 0) {
                    sb.append(" or ");
                }
                sb.append(like);
                objectList.add("%" + text.charAt(i) + "%");
            }
            if (!DbType.ORACLE.getDb().equals(databaseName) && text.length() > 0) {
                sb.append(" ) ");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    // 处理关闭连接时可能发生的异常
                }
            }
        }
        log.info("\n执行SQL：" + sb + "\n参数：" + JSONObject.toJSONString(objectList));
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sb.toString(), objectList.toArray());
        List<String> listSeq = new ArrayList<>();
        for (int i = 0; i < maps.size(); i++) {
            int i1 = countCommonChars(text, maps.get(i).get("COLUMN_COMMENT").toString());
            if (maps.get(i).get("COLUMN_COMMENT") == null || i1 < 2) {
                continue;
            }
            listSeq.add(i1 + "-" + i);
        }

        List<String> ex = new ArrayList<>();
        List<Map<String, Object>> resp = new ArrayList<>();
        // 使用自定义Comparator进行降序排序，只考虑第一个数字
        Collections.sort(listSeq, (o1, o2) -> {
            // 提取第一个数字并转换为整数进行比较
            int num1 = Integer.parseInt(o1.split("-")[0]);
            int num2 = Integer.parseInt(o2.split("-")[0]);
            // 降序排序：如果num1大于num2，则返回-1（即o1应该排在o2前面）
            return Integer.compare(num2, num1);
        });
        boolean ext = false;
        for (int i = 0; i < listSeq.size(); i++) {
            String[] split = listSeq.get(i).split("-");
            Map<String, Object> stringObjectMap = maps.get(Integer.parseInt(split[1]));
            if (text.equals(stringObjectMap.get("COLUMN_COMMENT"))) {
                ext = true;
            }
            if (!ex.contains(stringObjectMap.get("COLUMN_NAME"))) {
                ex.add(stringObjectMap.get("COLUMN_NAME").toString());
                resp.add(stringObjectMap);
            }
        }
        if (!ext) {
            String underline = chineseToEnglish.getUnderline(text);
            Map<String, Object> map = new HashMap<>();
            map.put("COLUMN_NAME", underline);
            map.put("COLUMN_COMMENT", text);
            resp.add(0, map);
        }
        return success(resp);
    }

    private static int countCommonChars(String str1, String str2) {
        // 使用HashSet存储str1中的字符，自动去重
        HashSet<Character> set = new HashSet<>();
        for (char c : str1.toCharArray()) {
            set.add(c);
        }

        int count = 0;
        // 遍历str2，检查每个字符是否在HashSet中
        for (char c : str2.toCharArray()) {
            if (set.contains(c)) {
                count++;
                // 如果只关心是否有相同的字符，而不是数量，这行代码可以省略
                // 但如果你不想重复计数相同的字符，则应该保留
                set.remove(c); // 从集合中移除已匹配的字符，避免重复计数
            }
        }

        return count;
    }

    /**
     * 获取数据库类型：Mysql、Oracle
     *
     * @param dbName
     * @return
     */
    @GetMapping({"/getDataBaseType"})
    public Result<?> getDataBaseType(String dbName) {
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getNewJdbcTemplateMap(dbName);
        String dataBaseType = SqlUtil.getDbType(jdbcTemplate);
        if (StringUtils.isNotBlank(dataBaseType)) {
            dataBaseType = SqlUtil.getMethodName(dataBaseType);
        }
        return success(dataBaseType, "操作成功！");
    }

    @GetMapping({"/getColumn"})
    public Result<?> find(String dbName, String tableName) {
        String sql;
        String sql2;
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getNewJdbcTemplateMap(dbName);
        String newDbName = SqlUtil.getNewDbName(dbName, jdbcTemplate);
        if (StringUtils.isNotBlank(newDbName)) {
            dbName = newDbName;
        }
        String dataBaseType = SqlUtil.getDbType(jdbcTemplate);
        jdbcTemplate.setFetchSize(1000);
        long starts = System.currentTimeMillis();
        Object[] data;
        if (DbType.ORACLE.getDb().equals(dataBaseType)) {
            OracleQuery oracleQuery = new OracleQuery();
            //sql = oracleQuery.getIFieldSql();
            sql = oracleQuery.getIFieldSql();
            sql2 = oracleQuery.tablesSql().toUpperCase() + " and table_name = ?";
            data = new Object[]{tableName};
        } else {
            MySqlQuery mySqlQuery = new MySqlQuery();
            sql = mySqlQuery.getIFieldSql();
            sql2 = mySqlQuery.getITableSql().toUpperCase() + " and table_name = ?";
            data = new Object[]{dbName, tableName};
        }
        String exSqlStr = getExecuteSql(sql, data);
        long start = System.currentTimeMillis();
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql, data);
        log.info("\n执行SQL1：" + exSqlStr + "\n执行SQL1耗时：" + (System.currentTimeMillis() - start));
        if (DbType.ORACLE.getDb().equals(dataBaseType)) {
            start = System.currentTimeMillis();
            List<Map<String, Object>> dataDefaultList = jdbcTemplate.queryForList("SELECT COLUMN_NAME,DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ? AND DATA_DEFAULT IS NOT NULL", data);
            log.info("\n执行dataDefaultList耗时：" + (System.currentTimeMillis() - start));
            start = System.currentTimeMillis();
            List<Map<String, Object>> dataPKList = jdbcTemplate.queryForList(OracleQuery.getPKSql().toUpperCase(), new Object[]{dbName, tableName});
            log.info("\n执行dataPKList耗时：" + (System.currentTimeMillis() - start));

            Map<String, Object> columnNames = new HashMap<>();
            for (Map<String, Object> dataDefault : dataDefaultList) {
                columnNames.put(dataDefault.get("COLUMN_NAME").toString(), dataDefault.get("DATA_DEFAULT"));
            }
            for (Map<String, Object> dataPK : dataPKList) {
                columnNames.put(dataPK.get("COLUMN_NAME").toString() + "_PK", dataPK.get("COLUMN_NAME"));
            }
            for (Map<String, Object> map : maps) {
                map.put("COLUMN_DEFAULT", columnNames.get(map.get("COLUMN_NAME")));
                map.put("is_pk", columnNames.get(map.get("COLUMN_NAME") + "_PK") == null ? 0 : 1);
                // map.put("CONSTRAINT_TYPE", columnNames.get(map.get("COLUMN_NAME")+"_PK"));
            }
        }
        Object[] objects = {dbName, tableName};
        exSqlStr = getExecuteSql(sql2, objects);
        start = System.currentTimeMillis();
        log.info("\n执行SQL2：\n" + exSqlStr );
        List<Map<String, Object>> maps1 = jdbcTemplate.queryForList(sql2, objects);
        Map<String, Object> table = maps1.get(0);
        log.info("执行SQL2耗时：" + (System.currentTimeMillis() - start));
        Map<String, Object> map = new HashMap<>();
        map.put("column", maps);
        map.put("table", table);
        map.put("databaseType", dataBaseType);
        log.info("\n获取数据库列总耗时：" + (System.currentTimeMillis() - starts));
        return success(map, "操作成功！");
    }

    @PostMapping({"/getData"})
    public Result<?> getData(@RequestBody QueryParameter queryParameter) throws JSQLParserException {
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getNewJdbcTemplateMap(queryParameter.getDbName());
        String sql = SqlUtil.getSelectSql(jdbcTemplate.getDataSource(), queryParameter.getTableName());
        queryParameter.setSql(sql);
        queryParameter.setHumpToLine(false);
        return success(crudServices.doGetData(queryParameter), queryParameter.getExecuteSql());
    }

    @PostMapping("/data/modify")
    public Result dataModify(@RequestBody ModifyParameter modifyParameter) {
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getJdbcTemplate(modifyParameter.getDbName());
        Object[] obj = OnlineUtils.getUpdateSql2(modifyParameter.getTableName(), modifyParameter.getColumnAndValueMap(), modifyParameter.getWhereMap());
        String sql = (String) obj[0];
        Object[] value = (Object[]) obj[1];
        String exSqlStr = getExecuteSql(sql, value);
        log.info("==>  Preparing:\n" + exSqlStr);
        int i = jdbcTemplate.update(sql, value);
        return i > 0 ? ResultUtil.success("更新成功！") : ResultUtil.error("更新失败！");
    }
    @PostMapping("/data/remove")
    public Result dataRemove(@RequestBody Map<String, Object> params) {
        TableDelete tableDelete = new TableDelete();
        tableDelete.setTableName(params.get("_TableName_").toString());
        tableDelete.setDbName(params.get("_DbName_").toString());
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getJdbcTemplate(tableDelete.getDbName());
        params.remove("_DbName_");
        params.remove("_TableName_");
        String deleteSql = OnlineUtils.getDeleteSql(tableDelete, params, true);
        log.info("==>  Preparing:\n" + deleteSql);
        log.info("==> Parameters:\n" + JSON.toJSONString(params.values()));
        Object [] obj = new Object[params.values().size()];
        int index = 0;
        for (String s : params.keySet()) {
            if(s.equals(params.get(s).toString())){
                throw new RuntimeException("非法删除！");
            }
        }
        for (Object value : params.values()) {
            obj[index] = value;
            index++;
        }
        int i = jdbcTemplate.update(deleteSql, obj);
        log.info("\n<==      Total: " + i);
        if (i == 0) {
            return ResultUtil.error("删除失败！");
        }
        return ResultUtil.success("删除成功！");
    }


    @PostMapping("/data/save")
    public Result dataSave(@RequestBody SaveParameter saveParameter) {
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getJdbcTemplate(saveParameter.getDbName());
        log.info("==> parameter:\n" + JSONObject.toJSONString(saveParameter));
        Object[] obj = OnlineUtils.getInsertSql2(saveParameter.getTableName(), saveParameter.getColumnAndValueMap(),saveParameter.getDbType());
        String sql = (String) obj[0];
        Object[] value = (Object[]) obj[1];
        String exSqlStr = getExecuteSql(sql, value);
        log.info("==>  Preparing:\n" + exSqlStr);
        int i = jdbcTemplate.update(sql, value);
        return i > 0 ? ResultUtil.success("保存成功！") : ResultUtil.error("保存失败！");
    }

    @PostMapping({"/preView"})
    @Transactional
    public Result<?> preView(@RequestBody DBDto dbDto) {
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getNewJdbcTemplateMap(dbDto.getDbName());
        Map<String, Map<String, Object>> dataSourceMap;
        String databaseName = SqlUtil.getDbType(jdbcTemplate);
        List<String> exSqlList = new ArrayList<>();
        if ("1".equals(dbDto.isAdd)) {
            if (DbType.ORACLE.getDb().equals(databaseName)) {
                exSqlList.add("CREATE TABLE " + dbDto.getTableName() + "( 欧莱普特殊建表字段 NUMBER)");
            } else {
                exSqlList.add("CREATE TABLE " + dbDto.getTableName() + "( 欧莱普特殊建表字段 INT)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            }
        }
        if (DbType.ORACLE.getDb().equals(databaseName)) {
            OracleQuery oracleQuery = new OracleQuery();
            String sql = oracleQuery.tableFieldsSql();
            List<Map<String, Object>> listMap = jdbcTemplate.queryForList(sql, new Object[]{dbDto.getTableName()});
            dataSourceMap = listMap.stream()
                    .collect(Collectors.toMap(
                            map -> map.get(oracleQuery.fieldName()).toString().toUpperCase(), // 键是COLUMN_NAME的值
                            map -> map,
                            (existing, replacement) -> replacement // 如果有冲突，保留最后一个（这里是replacement）
                    ));
            oracleAsyncDBService.getSql(dbDto, dataSourceMap, exSqlList);
        } else {  //获取字段是否存在
            String sql = "select COLUMN_NAME as COLUMN_NAME_OLD,COLUMN_COMMENT,COLUMN_TYPE,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT from information_schema.COLUMNS where " +
                    "TABLE_SCHEMA = ? and TABLE_NAME = ?";

            String dbName = dbDto.getDbName();
            String newDbName = SqlUtil.getNewDbName(dbName, jdbcTemplate);
            if (StringUtils.isNotBlank(newDbName)) {
                dbName = newDbName;
            }
            List<Map<String, Object>> listMap = jdbcTemplate.queryForList(sql, new Object[]{dbName, dbDto.getTableName()});
            dataSourceMap = listMap.stream()
                    .collect(Collectors.toMap(
                            map -> map.get("COLUMN_NAME").toString().toLowerCase(), // 键是COLUMN_NAME的值
                            map -> map,
                            (existing, replacement) -> replacement // 如果有冲突，保留最后一个（这里是replacement）
                    ));
            asyncDBServices.getSql(dbDto, dataSourceMap, exSqlList);
        }
        return success(exSqlList);
    }

    @PostMapping({"/execute"})
    @Transactional
    public Result<?> execute(@RequestBody DBDto dbDto) {
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getNewJdbcTemplateMap(dbDto.getDbName());
        log.info("开始执行SQL");
        for (String sqls : dbDto.getSql()) {
            try {
                log.info(sqls);
                jdbcTemplate.execute(sqls);
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException("执行SQL异常：" + sqls);
            }
        }
        log.info("结束执行SQL");
        return success("保存成功！");
    }

    @PostMapping({"/save"})
    @Transactional
    public Result<?> save(@RequestBody DBDto dbDto) {
        JdbcTemplate jdbcTemplate = jdbcTemplateSource.getNewJdbcTemplateMap(dbDto.getDbName());
        Map<String, Map<String, Object>> dataSourceMap;
        String databaseName = SqlUtil.getDbType(jdbcTemplate);
        List<String> exSqlList = new ArrayList<>();
        if ("1".equals(dbDto.isAdd)) {
            if (DbType.ORACLE.getDb().equals(databaseName)) {
                exSqlList.add("CREATE TABLE " + dbDto.getTableName() + "( 欧莱普特殊建表字段 NUMBER)");
            } else {
                exSqlList.add("CREATE TABLE " + dbDto.getTableName() + "( 欧莱普特殊建表字段 INT)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
            }
        }
        if (DbType.ORACLE.getDb().equals(databaseName)) {
            OracleQuery oracleQuery = new OracleQuery();
            String sql = oracleQuery.tableFieldsSql();
            List<Map<String, Object>> listMap = jdbcTemplate.queryForList(sql, new Object[]{dbDto.getTableName()});
            dataSourceMap = listMap.stream()
                    .collect(Collectors.toMap(
                            map -> map.get(oracleQuery.fieldName()).toString().toUpperCase(), // 键是COLUMN_NAME的值
                            map -> map,
                            (existing, replacement) -> replacement // 如果有冲突，保留最后一个（这里是replacement）
                    ));
            oracleAsyncDBService.getSql(dbDto, dataSourceMap, exSqlList);
        } else {  //获取字段是否存在
            String sql = "select COLUMN_NAME as COLUMN_NAME_OLD,COLUMN_COMMENT,COLUMN_TYPE,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,IS_NULLABLE,COLUMN_DEFAULT from information_schema.COLUMNS where " +
                    "TABLE_SCHEMA = ? and TABLE_NAME = ?";

            String dbName = dbDto.getDbName();
            String newDbName = SqlUtil.getNewDbName(dbName, jdbcTemplate);
            if (StringUtils.isNotBlank(newDbName)) {
                dbName = newDbName;
            }
            List<Map<String, Object>> listMap = jdbcTemplate.queryForList(sql, new Object[]{dbName, dbDto.getTableName()});
            dataSourceMap = listMap.stream()
                    .collect(Collectors.toMap(
                            map -> map.get("COLUMN_NAME").toString().toLowerCase(), // 键是COLUMN_NAME的值
                            map -> map,
                            (existing, replacement) -> replacement // 如果有冲突，保留最后一个（这里是replacement）
                    ));
            asyncDBServices.getSql(dbDto, dataSourceMap, exSqlList);
        }

        log.info("开始执行SQL");
        for (String sqls : exSqlList) {
            try {
                log.info(sqls);
                jdbcTemplate.execute(sqls);
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException("执行SQL异常：" + sqls);
            }
        }
        log.info("结束执行SQL");
        return success("保存成功！");
    }
}
